﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebShop.Services;
using System.Web.Script.Serialization;
using WebShop.Models;
using System.Text;
using System.Data.SqlClient;

namespace WebShop.Controllers
{
    public class ProductController : Controller
    {
        //
        // GET: /Product/

        PerfumesService _perfumesService = new PerfumesService();

        //public ProductController(PerfumesService perfumesService)
        //{
        //    _perfumesService = perfumesService;
        //}

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Perfumes()
        {
            var perfumes = _perfumesService.GetAllPerfumes();

            const int perfumeCategory = 1;

            JavaScriptSerializer serializer = new JavaScriptSerializer();
            var data = serializer.Serialize(perfumes);

            ViewData["FilterGroups"] = GetFilteringObjects(perfumeCategory);
            return View(perfumes);
        }

        //must be moved to common controller for example 'filtering'
        public string GetFilteringObjects(int id)
        {
            using (WebShop.DataSource.WebShopEntities context = new DataSource.WebShopEntities())
            {
                var filterGroups = new List<FilterGroupModel>();

                var subcategoryGroup = new FilterGroupModel { Name = "Brands" };
                var subcategories = context.Subcategories.Where(sc => sc.FK_Category == id).Select(s => new FilterPropertyModel
                {
                    PropertyName = s.Subcategory,
                    Count = s.Products.Count,
                    Value = s.Id,
                    IsSubcategory = true
                }).ToList();
                subcategoryGroup.Properties = subcategories;
                filterGroups.Add(subcategoryGroup);

                var properties = context.Categories.SingleOrDefault(c => c.Id == id).Properties;
                foreach (var property in properties)
                {
                    var propertyGroup = new FilterGroupModel 
                    {
                        Name = property.Name
                    };

                    var filterProperties = property.PropertyFixedValues.Select(f => new FilterPropertyModel {
                        PropertyName = f.Value,
                        Value = f.Id,
                        Count = f.ProductPropertyValues.Count,
                        IsSubcategory = false
                    }).ToList();

                    propertyGroup.Properties = filterProperties;
                    filterGroups.Add(propertyGroup);
                }

                JavaScriptSerializer serializer = new JavaScriptSerializer();
                return serializer.Serialize(filterGroups);
            }
        }

        [HttpPost]
        public JsonResult GetProductsBySubcategory(int id)
        {
            using (WebShop.DataSource.WebShopEntities context = new DataSource.WebShopEntities())
            {
                var products = context.Products.Where(p => p.FK_Subcategory == id).Select(p => new PerfumeModel { 
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    SubcategoryId = p.FK_Subcategory
                }).ToList();
                return Json(products);
            }
        }

        [HttpPost]
        public JsonResult GetFilteredData(List<FilterPropertyModel> filterProperties, int groupCount)
        {
            using (WebShop.DataSource.WebShopEntities context = new DataSource.WebShopEntities())
            {
                int categoryId = 1;   
                if (filterProperties == null)
                {
                    //return all products for current category
                    List<ProductModel> allProducts = context.Products.Where(p => p.Subcategories.FK_Category == categoryId).Select(p => new ProductModel {
                        Id = p.Id,
                        Name = p.Name,
                        Price = p.Price,
                        SubcategoryId = p.FK_Subcategory
                    }).ToList();
                    return Json(allProducts);
                }

                StringBuilder builder = new StringBuilder(@"select distinct P.* 
                                                            from Products P
                                                            join ProductPropertyValues PV on PV.FK_Product=P.Id
                                                            join Properties PR on PR.Id=PV.FK_Property
                                                            join PropertyFixedValues FV on FV.Id=Pv.FK_PropertyFixedValue
                                                            where PR.FK_Category=1 ");
                int i = 0;
                List<string> subcategories = new List<string>();
                List<string> properties = new List<string>();
                List<SqlParameter> parameters = new List<SqlParameter>();
                foreach (var property in filterProperties)
                {
                    if (property.IsSubcategory)
                    {
                        subcategories.Add("P.FK_Subcategory=@param" + i);
                    }
                    else
                    {
                        properties.Add("PV.FK_PropertyFixedValue=@param" + i);
                    }

                    parameters.Add(new SqlParameter { ParameterName = "@param" + i, Value = property.Value });
                    i++;
                }

                if (subcategories.Count > 0)
                {
                    builder.Append(" AND (" + string.Join(" OR ", subcategories) + ")");
                }

                if (properties.Count > 0)
                {
                    builder.Append(" AND (" + string.Join(" OR ", properties) + ")");
                }

                if (groupCount > 0)
                {
                    builder.Append(@"group by P.Id,P.FK_Subcategory,P.Name,P.Price,P.Description
                                    having COUNT(1)=" + groupCount);
                }

                var products = context.ExecuteStoreQuery<DataSource.Products>(builder.ToString(), parameters.ToArray())
                .Select(p => new Models.ProductModel 
                {
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    SubcategoryId = p.FK_Subcategory
                }).ToList();

                return Json(products);
            }
        }

    }
}
